import string
import time

import CONST
from persistence import sqlUtil
from pypinyin import lazy_pinyin
from bisect import bisect_right

db_name = 'kw_cpt_word'


def insert_all_words():
    """
    用于将分词结果存入数据库
    """
    # 读取预处理后的数据集
    with open('../static/after_cut_word.txt', 'r', encoding='utf-8') as file:
        datas = file.readlines()
        wordlist = []
        for line in datas:
            line = line[:-1]  # 去除词条尾行的\n
            # 如果line为‘’直接退出
            if not line:
                continue
            words = line.split('\t')
            # 过滤words中的''值
            words = list(filter(lambda x: x.strip(), words))
            wordlist.extend(words)
    # 将wordlist中的词除去重复元素
    wordlist = {}.fromkeys(wordlist).keys()
    # print(wordlist)

    # 将单词按照a-z，26个字母分类，还有一个other
    # 初始化字典
    word_dict = {key: list() for key in string.ascii_lowercase}
    word_dict['other'] = []
    for word in wordlist:
        start_alpha = lazy_pinyin(word)[0][0]
        if start_alpha in string.ascii_lowercase:
            word_dict[start_alpha].append(word)
        else:
            word_dict['other'].append(word)
    # print(word_dict)
    # 将数据存入数据库
    sql = 'INSERT INTO word_{} (id, word) VALUES ({}, %s);'
    index = 0
    try:
        conn, cur = sqlUtil.get_cur(db_name)
        for k, v in word_dict.items():
            for ite in v:
                cur.execute(sql.format(k, index), ite)
                index += 1
            conn.commit()
    except Exception as e:
        conn.rollback()
        print('事务处理失败', e)
    finally:
        sqlUtil.close_db(conn, cur)


def get_word_id(word):
    """
    在词典中查找单词的id
    :param word: 待查找的单词
    :return: 单词的id
    """
    start_alpha = lazy_pinyin(word)[0][0]
    if start_alpha in string.ascii_lowercase:
        sql = 'SELECT id FROM word_{} WHERE word=%s'.format(start_alpha)
    else:
        sql = 'SELECT id FROM word_{} WHERE word=%s'.format('other')
    try:
        conn, cur = sqlUtil.get_cur(db_name)
        cur.execute(sql, word)
        results = cur.fetchall()
    except Exception as e:
        conn.rollback()
        print('事务处理失败', e)
    finally:
        sqlUtil.close_db(conn, cur)
    return results[0][0]

def get_mid_word(id):
    """
    查找中介词
    :param id: 待查找的单词id
    :return: 单词的id
    """
    sql = 'SELECT * FROM mid_word WHERE id={}'.format(id)

    try:
        conn, cur = sqlUtil.get_cur(db_name)
        cur.execute(sql)
        results_mid = cur.fetchall()
        # for row in results:
        #     print(row)
    except Exception as e:
        conn.rollback()
        print('事务处理失败', e)
    finally:
        sqlUtil.close_db(conn, cur)
    return results_mid[0]


def get_lt_word(id):
    """
    查找中介词
    :param id: 待查找的单词id
    :return: 单词的id
    """
    sql = 'SELECT * FROM lt_word WHERE id={}'.format(id)

    try:
        conn, cur = sqlUtil.get_cur(db_name)
        cur.execute(sql)
        results_lt = cur.fetchall()
    except Exception as e:
        conn.rollback()
        print('事务处理失败', e)
    finally:
        sqlUtil.close_db(conn, cur)
    return results_lt[0]
def get_cpt_word(id):
    """
    查找中介词
    :param id: 待查找的单词id
    :return: 单词的id
    """
    sql = 'SELECT * FROM cpt_word WHERE id={}'.format(id)

    try:
        conn, cur = sqlUtil.get_cur(db_name)
        cur.execute(sql)
        results_cpt = cur.fetchall()
        # for row in results:
        #     print(row)
    except Exception as e:
        conn.rollback()
        print('事务处理失败', e)
    finally:
        sqlUtil.close_db(conn, cur)
    return results_cpt

def get_id_to_word(id):
    """
    在词典通过id查询单词
    :param id: 待查找的id
    :return: 单词
    """
    local_list = [alp for alp in string.ascii_lowercase] + ['other']
    index = bisect_right(CONST.WORD_INDEX, id) - 1
    start_alpha = local_list[index]

    sql = 'SELECT word FROM word_{} WHERE id={}'.format(start_alpha, id)

    try:
        conn, cur = sqlUtil.get_cur(db_name)
        cur.execute(sql)
        results = cur.fetchall()
    except Exception as e:
        conn.rollback()
        print('事务处理失败', e)
    finally:
        sqlUtil.close_db(conn, cur)
    return results[0][0]


def save_searched_comp(seed, mid_with_comp):
    """
    将检索的竞争关键词存入数据库
    :param seed: 种子关键词
    :param mid_with_comp: 竞争关键词字典
    """
    seed = get_word_id(seed)
    # 首先处理该字典，将其转化为能够直接存储的类型
    temp_list = []
    for k, v in mid_with_comp.items():
        if not v:  # 去除空字典
            continue
        str1 = ''
        str2 = ''
        for word in v:
            str1 = f'{str1}|{get_word_id(word[0])}'
            str2 = f'{str2}|{word[1]}'
        temp_list.append((get_word_id(k), str1[1:], str2[1:]))
    # print(temp_list)
    sql = 'INSERT INTO cpt_word (id, mid_id, cpt_key, metric) VALUES ({}, {}, %s, %s);'
    try:
        conn, cur = sqlUtil.get_cur(db_name)
        for mid, comp_key, comp in temp_list:
            cur.execute(sql.format(seed, mid), (comp_key, comp))
        conn.commit()
    except Exception as e:
        conn.rollback()
        print('事务处理失败', e)
    finally:
        sqlUtil.close_db(conn, cur)


def save_searched_mid(seed, mid_key):
    """
    将检索的中介关键词存入数据库
    :param seed: 种子关键词
    :param mid_key: 中介关键列表
    """
    seed = get_word_id(seed)
    # 首先处理该列表，将其转化为能够直接存储的类型
    str1 = ''
    str2 = ''
    for word, metric in mid_key:
        if not word:  # 去除空字
            continue
        str1 = f'{str1}|{get_word_id(word)}'
        str2 = f'{str2}|{metric}'
    sql = 'INSERT INTO mid_word (id, mid_key, metric) VALUES ({}, %s, %s);'.format(seed)
    try:
        conn, cur = sqlUtil.get_cur(db_name)
        cur.execute(sql, (str1, str2))
        conn.commit()
    except Exception as e:
        conn.rollback()
        print('事务处理失败', e)
    finally:
        sqlUtil.close_db(conn, cur)


def save_searched_long_tail(seed, long_tail):
    """
    将检索的中介关键词存入数据库
    :param seed: 种子关键词
    :param long_tail: 长尾关键词
    """
    seed = get_word_id(seed)
    # 首先处理该列表，将其转化为能够直接存储的类型
    str1 = ''
    for word in long_tail:
        if not word:  # 去除空字
            continue
        str1 = f'{str1}|{word}'
    sql = 'INSERT INTO lt_word (id, lt_word) VALUES ({}, %s);'.format(seed)
    try:
        conn, cur = sqlUtil.get_cur(db_name)
        cur.execute(sql, str1)
        conn.commit()
    except Exception as e:
        conn.rollback()
        print('事务处理失败', e)
    finally:
        sqlUtil.close_db(conn, cur)


def save_searched_seed(seed):
    """
    将检索的种子关键词存入数据库
    :param seed: 种子关键词
    """
    seed_id = get_word_id(seed)
    now = time.strftime("%Y-%m-%d", time.localtime())
    sql = 'INSERT INTO keyword (id, word, update_time) VALUES ({}, %s, %s);'.format(seed_id)
    try:
        conn, cur = sqlUtil.get_cur(db_name)
        cur.execute(sql, (seed, now))
        conn.commit()
    except Exception as e:
        conn.rollback()
        print('事务处理失败', e)
    finally:
        sqlUtil.close_db(conn, cur)


if __name__ == '__main__':
    # insert_all_words()

    # id = get_word_id('班')
    # print(id)

    # save_searched_comp('三星', {'三星': [('班', 0.00213), ('奔驰', 0.0124)]})

    # save_searched_seed('三星')

    print(get_id_to_word(146286))

    # get_lt_word(6)